package com.example.spring;

import com.example.spring.bean.User;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * ClassName:test
 * Package:com.example.spring
 * Description:
 *
 * @Author:ZhangXin
 * @Create:2023/5/10-10:57
 * @Version:v1.0
 */
public class test {

    @Test
    public void testCallback() {
        //可以使用callback回调函数
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        String sql ="select id , real_name,age from user where id=?";
        //注册回调函数,当execute方法执行的时候,回调函数中的 doInPreparedStatement() 会被调用
        User user = jdbcTemplate.execute(sql, new PreparedStatementCallback<User>() {

            @Override
            public User doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                User user = null;
                ps.setInt(1, 8);
                ResultSet rs = ps.executeQuery();
                if (rs.next()) {
                    int id = rs.getInt("id");
                    String realName = rs.getString("real_name");
                    int age = rs.getInt("age");
                    user = new User(id, realName, age);
                }
                return user;
            }
        });
        System.out.println(user);
    }

    @Test
    public void testDeleteBatch() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        String sql = "delete from user where id=? ";

        Object[] objs1 ={1};
        Object[] objs2 ={2};
        Object[] objs3 ={3};
        Object[] objs4 ={5};

        List<Object[]> list = new ArrayList<>();
        Collections.addAll(list,objs1, objs2, objs3, objs4);

        int[] ints = jdbcTemplate.batchUpdate(sql, list);
        System.out.println(Arrays.toString(ints));
//        for (int anInt : ints) {
//            System.out.println(anInt);
//        }
    }

    @Test
    public void testUpdateBatch() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        String sql = "update user set real_name=?,age=? where id =?";
        Object[] objs1 ={"唐三",23,9};
        Object[] objs2 ={"唐昊",23,10};
        Object[] objs3 ={"唐晨",23,11};
        Object[] objs4 ={"唐啸",23,12};

        List<Object[]> list = new ArrayList<>();
        Collections.addAll(list,objs1, objs2, objs3, objs4);

        int[] ints = jdbcTemplate.batchUpdate(sql, list);
        for (int anInt : ints) {
            System.out.println(anInt);
        }
    }

    @Test//批量添加
    public void testAddBatch() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        String sql = "insert into user(real_name,age) values(? ,?)";


        //准备数据
        Object[] objs1 = {"小花", 29};
        Object[] objs2 = {"小嫒", 29};
        Object[] objs3 = {"小佧", 29};
        Object[] objs4 = {"小晓", 29};

        List<Object[]> list = new ArrayList<>();
        Collections.addAll(list, objs1, objs2, objs3, objs4);

        int[] ints = jdbcTemplate.batchUpdate(sql, list);
        for (int anInt : ints) {
            System.out.println(anInt);
        }

    }

    @Test
    public void queryOneValue() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        String sql = "select count(*) from user";

        Integer total = jdbcTemplate.queryForObject(sql, int.class);
        System.out.println("总记录条数:" + total);
    }

    @Test
    public void select() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        String sql = "SELECT * FROM user;";
        List<User> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
        users.forEach(user -> System.out.println(user));
    }

    @Test
    public void selectById() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);


        String sql = "SELECT * FROM user WHERE id=?;";

        //new BeanPropertyRowMapper<>(User.class) 数据库和实体类之间的映射关系
        User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), 1);
        System.out.println(user);
    }

    @Test
    public void delete() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        String sql = "DELETE FROM user WHERE id = ?";

        int count = jdbcTemplate.update(sql, 4);
        System.out.println(count);
    }

    @Test
    public void update() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        String sql = "UPDATE db_user.user SET real_name=? ,age=? WHERE id=?";
        int count = jdbcTemplate.update(sql, "詹少干", 25, 4);
        System.out.println(count);
    }

    @Test
    public void insert() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        String sql = "INSERT INTO db_user.user(real_name,age) VALUES(?,?)";

        int count = jdbcTemplate.update(sql, "小明", 25);
        System.out.println(count);
    }

    @Test
    public void testJDBC() {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        System.out.println(jdbcTemplate);
    }
}
